﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CqusInfo.RAL.User;
using CqusInfo.CR.SqlHelper;
using System.Data.SQLite;
using System.Data;

namespace CqusInfo.DAL.User
{
    /// <summary>
    /// tb_User 
    /// </summary>
    public class UserDAL:Interface.IUserDAL
    {
        public Int32 GetCounts(bool isDel)
        {
            return SQLiteHelper.GetCounts("tb_User", isDel);
        }

        public List<UserModel> Query(Int32 page, Int32 takeItem, bool isDel)
        {
            List<UserModel> userList = new List<UserModel>();
            DataSet ds = SQLiteHelper.Query("tb_User", "c_b_isDel", isDel, page, takeItem);
            string cacheId = string.Format("Query_ALL_{0}_{1}", page, takeItem);

            userList = SqlData.GetQueryList<UserModel>(cacheId, "UserModel", ds);

            return userList;
        }

        public Int32 Add(UserModel model)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("insert into tb_User values(");
            sqlStr.Append("@c_vc_userName,@c_vc_passWord,@c_vc_email,@c_b_isAlive,@c_b_isLock,@c_nvc_nickName,@c_b_isComplete,@c_dt_regDate,@c_b_isDel)");
            SQLiteParameter[] parameters = { 
                                       new SQLiteParameter("@c_vc_userName",DbType.String),
                                       new SQLiteParameter("@c_vc_passWord",DbType.String),
                                       new SQLiteParameter("@c_vc_email",DbType.String),
                                       new SQLiteParameter("@c_b_isAlive",DbType.Boolean),                                       
                                       new SQLiteParameter("@c_b_isLock",DbType.Boolean),
                                       new SQLiteParameter("@c_nvc_nickName",DbType.String),
                                       new SQLiteParameter("@c_b_isComplete",DbType.Boolean),
                                       new SQLiteParameter("@c_dt_regDate",DbType.DateTime),
                                       new SQLiteParameter("@c_b_isDel",DbType.Boolean)
                                       };
            parameters[0].Value = model.c_vc_userName;
            parameters[1].Value = model.c_vc_passWord;
            parameters[2].Value = model.c_vc_email;
            parameters[3].Value = model.c_b_isAlive;
            parameters[4].Value = model.c_b_isLock;
            parameters[5].Value = model.c_nvc_nickName;
            parameters[6].Value = model.c_b_isComplete;
            parameters[7].Value = model.c_dt_regDate;
            parameters[8].Value = false;

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters);
        }

        public Int32 Delete(string userName,bool isDel,bool trueDel)
        {
            if (trueDel)
                return SQLiteHelper.Delete("tb_User", "c_vc_userName", userName);
            else
                return SQLiteHelper.Delete("tb_User", "c_vc_userName", userName, "c_b_isDel", isDel);
        }

        public Int32 Update_Password(string userName,string passWord)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("update tb_User set ");
            sqlStr.Append("c_vc_passWord=@passWord where c_vc_userName=@userName");
            SQLiteParameter[] parameters = { 
                                       new SQLiteParameter("@userName",DbType.String),
                                       new SQLiteParameter("@passWord",DbType.String)
                                       };
            parameters[0].Value = userName;
            parameters[1].Value = passWord;

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters);
        }
    }
}
